package com.example.texted.poi;

import org.apache.poi.ss.usermodel.*;
import org.junit.Test;

import java.io.InputStream;
import java.util.Iterator;

public class ExcelTest {
    @Test
    public void main() {
        testExcel();
    }
    @Test
    public void testExcel() {
        //只需要引入 org.apache.poi/poi-ooxml
//        try(InputStream resource = this.getClass().getClassLoader().getResourceAsStream("test1.xls")) {
        try(InputStream resource = this.getClass().getClassLoader().getResourceAsStream("test1.xlsx")) {
            //同时支持Excel 2003、2007
            if (resource == null) {
                return;
            }
            Workbook workbook = WorkbookFactory.create(resource); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int numberOfSheets = workbook.getNumberOfSheets();
            System.out.println("getNumberOfSheets："+numberOfSheets);
            Sheet sheet = workbook.getSheetAt(0);
            System.out.println("getPhysicalNumberOfRows:"+sheet.getPhysicalNumberOfRows());
            System.out.println("getLastRowNum:"+sheet.getLastRowNum());
            //int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
            int rowCount = sheet.getLastRowNum() + 1; //获取总行数
            for (int i = 0; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                int cellCount;
                if (row != null) {//row  该行所有单元格为空时，row是null值
                    //cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    cellCount = row.getLastCellNum();//获取最后一个不为空 的列是第几个。
                    System.out.println( i +"索引行getPhysicalNumberOfCells:" + row.getPhysicalNumberOfCells());
                    System.out.println( i +"索引行getLastCellNum:" + cellCount);

                    //有空列要用右键删除 delete无法去除
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell next = cellIterator.next();
                        System.out.println(next.getStringCellValue());
                    }

                    cellCount = Math.max(cellCount, 0);//getLastCellNum没有单元格时会返回负数
                    System.out.println( i +"索引行getLastCellNum:" + cellCount);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
